import time,re
import pandas as pd
import sys
import redis
import pickle
import logging
import hashlib
import configparser
import datetime
from sqlalchemy import create_engine, DateTime, String,text
import pymysql
import requests
import os
import pywencai
import akshare as ak
import pandas as pd
from datetime import datetime,timedelta
import traceback
pymysql.install_as_MySQLdb()

os.environ['PATH'] = '/home/chencan/node/bin:$PATH";'
log_format = "%(asctime)s - %(levelname)s - %(filename)s:%(lineno)d - %(message)s"
date_format = "%Y-%m-%d %H:%M:%S"  # 精确到秒
logging.basicConfig(level=logging.DEBUG, format=log_format, datefmt=date_format)

# 日志文件路径
log_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'application.log')

# 创建一个 handler，用于写入日志文件
file_handler = logging.FileHandler(log_file_path)
file_handler.setFormatter(logging.Formatter(log_format, date_format))
# 添加 handler 到 logger
logging.getLogger().addHandler(file_handler)

# 初始化配置解析器
config = configparser.ConfigParser()
current_dir = os.path.dirname(os.path.abspath(__file__))
config.read(current_dir+'/config.ini', encoding='utf-8')


# 获取Redis的配置信息
redis_host = config.get('Redis', 'host')
redis_port = config.getint('Redis', 'port')
redis_db = config.getint('Redis', 'db')
redis_password = config.get('Redis', 'password')
r = redis.Redis(host=redis_host, port=redis_port, db=redis_db, password=redis_password)

mysql_port = config.getint('mysql', 'port')
mysql_host = config.get('mysql', 'host')
mysql_db = config.get('mysql', 'db')
import urllib.parse
mysql_password = urllib.parse.quote(config.get('mysql', 'password'))
mysql_user = config.get('mysql', 'user')
db_url = f'mysql://{mysql_user}:{mysql_password}@{mysql_host}:{mysql_port}/{mysql_db}'

engine = create_engine(db_url,pool_size=20,max_overflow=20,pool_recycle=60)



def get_next_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    # 筛选出所有晚于给定交易日的日期
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] > pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=True)  # 按日期升序排序
    next_trade_date = date_df["trade_date"].values[0]  # 获取最接近给定日期的下一个交易日
    return next_trade_date.strftime("%Y%m%d")  # 格式化日期

# 定义获取前一个交易日的函数
def get_pre_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[0]
    return pre_trade_date.strftime("%Y%m%d")

def get_pre_trade_date_n(trade_date,n):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[n-1]
    return pre_trade_date.strftime("%Y%m%d")


#记录前一天的数据
def PanQian(query_date):
    pre_trade_date = get_pre_trade_date(query_date)
    next_trade_date = get_next_trade_date(query_date)
    last_year_date = get_pre_trade_date_n(query_date,200)
    last_60_date = get_pre_trade_date_n(query_date, 60)

    question =f'''
        {pre_trade_date}涨跌幅，概念，行业，{pre_trade_date}连续涨停天数，{pre_trade_date}市值,{pre_trade_date}成交额,{pre_trade_date}的收盘价,
        {pre_trade_date}的5日均线,{pre_trade_date}的10日均线,{pre_trade_date}的20日均线,或者{last_year_date}到{pre_trade_date}的涨停次数
    '''

    logging.info(f"问句:{question}")
    df = pywencai.get(query=question,loop=True)
    logging.info(f"获得的列名:{df.columns}")
    df["股票代码"] = df["股票代码"].str[0:6]


    df.rename({f"连续涨停天数[{pre_trade_date}]": "连续涨停天数"}, axis=1, inplace=True)
    df.rename({f"涨跌幅:前复权[{pre_trade_date}]":"涨跌幅"}, axis=1, inplace=True)
    df.rename({f"a股市值(不含限售股)[{pre_trade_date}]":"流通市值"}, axis=1, inplace=True)
    df.rename({f"收盘价:不复权[{pre_trade_date}]":"收盘价"}, axis=1, inplace=True)
    df.rename({f"成交额[{pre_trade_date}]":"成交额"}, axis=1, inplace=True)
    df.rename({f"5日均线[{pre_trade_date}]": "5日均线"}, axis=1, inplace=True)
    df.rename({f"10日均线[{pre_trade_date}]": "10日均线"}, axis=1, inplace=True)
    df.rename({f"20日均线[{pre_trade_date}]": "20日均线"}, axis=1, inplace=True)
    df.rename({f"涨停次数[{last_year_date}-{pre_trade_date}]": "近一年涨停次数"}, axis=1, inplace=True)


    df['连续涨停天数'] = df['连续涨停天数'].fillna(0)
    df["流通市值"]  = df["流通市值"].astype(float)
    df["流通市值"] = round(df["流通市值"]/100000000,2)
    df["成交额"]  = df["成交额"].astype(float)
    df["成交额"] = round(df["成交额"]/100000000,2)
    df["涨跌幅"]  = df["涨跌幅"].astype(float)
    df["收盘价"] = df["收盘价"].astype(float)

    # 新股可能没有均线
    df['5日均线'] = df['5日均线'].replace('', 1)
    df["5日均线"] = df["5日均线"].astype(float)
    df['10日均线'] = df['10日均线'].replace('', 1)
    df["10日均线"] = df["10日均线"].astype(float)
    df['20日均线'] = df['20日均线'].replace('', 1)
    df["20日均线"] = df["20日均线"].astype(float)


    df["5日乖离"] = round((df["收盘价"]-df["5日均线"])*100/df["5日均线"],2)
    df["10日乖离"] = round((df["收盘价"] - df["10日均线"]) * 100 / df["10日均线"], 2)
    df["20日乖离"] = round((df["收盘价"] - df["20日均线"]) * 100 / df["20日均线"], 2)

    df = df[["股票代码","股票简称","所属概念","所属同花顺行业","连续涨停天数","涨跌幅","流通市值","收盘价","成交额","5日乖离","10日乖离","20日乖离","近一年涨停次数"]]

    #将涨停次数这一列的空值填充为0
    df['近一年涨停次数'] = df['近一年涨停次数'].fillna(0)

    df["日期"] = query_date
    r.set(f"stock_panqian:{query_date}", pickle.dumps(df))
    r.expire(f"stock_panqian:{query_date}", 60*60*24*2)

    with engine.connect() as connection:
        sql = f"DELETE FROM stock_fupan WHERE 日期='{query_date}'"
        print(sql)
        delete_statement = text(sql)
        result = connection.execute(delete_statement)
        connection.commit()
        print(result.rowcount, "rows deleted.")
        df.to_sql("stock_fupan",engine, if_exists='append', index=False)

    return df

#查看负反馈是否强烈
def QingXu(query_date):
    question = f"{query_date}竞价跌幅前50,{query_date}涨跌幅，非北交所，非ST，非科创，上市时间大于365天"
    print(question)
    df = pywencai.get(question=question)

    df = df.rename(columns={f'涨跌幅:前复权[{query_date}]': '涨跌幅', f'分时涨跌幅:前复权[{query_date} 09:25]': '竞价'})

    df = df.head(10)
    df["竞价"]  = df["竞价"].astype(float)
    df["竞价"] = round(df["竞价"],2)
    df = df[["股票代码", "股票简称", "竞价"]]
    return df


def getZhaBan(query_date):
    # 获取前两个交易日
    pre_trade_date = get_pre_trade_date(query_date)
    pre_2_trade_date = get_pre_trade_date(pre_trade_date)

    # 构造查询问句
    question = f'''
    {pre_2_trade_date}涨停,非北交所,{pre_trade_date}长上影或者炸板或者收阴线，非ST，{query_date}集合竞价涨跌幅,{pre_2_trade_date}收盘价,{pre_trade_date}收盘价,
    {pre_trade_date}成交额,{pre_2_trade_date}连续涨停天数,{query_date}竞价成交额,{pre_trade_date}市值，行业，概念
    '''
    print(question)

    # 使用问财获取数据
    df = pywencai.get(question=question)
    # print(df)
    # print(df.columns)

    # 排序并重命名列
    df = df.sort_values(by=f"分时涨跌幅:前复权[{query_date} 09:25]", ascending=False)
    df.rename({f"分时涨跌幅:前复权[{query_date} 09:25]": "竞价涨跌幅"}, axis=1, inplace=True)

    df.rename({f"连续涨停天数[{pre_2_trade_date}]": "连板"}, axis=1, inplace=True)
    df["连板"] = df["连板"].astype(int)

    df["竞价涨跌幅"] = df["竞价涨跌幅"].astype(float)
    df = df.head(10)
    df = df[df["竞价涨跌幅"] > 0]

    df.rename({f"a股市值(不含限售股)[{pre_trade_date}]": "市值"}, axis=1, inplace=True)
    df["市值"] = df["市值"].astype(float)
    df["市值"] = round(df["市值"] / 100000000, 2)

    df["跌幅"] = round((df[f"收盘价:不复权[{pre_trade_date}]"] - df[f"收盘价:不复权[{pre_2_trade_date}]"]) * 100 / df[
        f"收盘价:不复权[{pre_2_trade_date}]"], 2)
    df.rename({f"成交额[{pre_trade_date}]": "昨日成交额"}, axis=1, inplace=True)
    df["昨日成交额"] = df["昨日成交额"].astype(float)
    df["昨日成交额"] = round(df["昨日成交额"] / 100000000, 2)

    df.rename({f"竞价金额[{query_date}]": "竞价金额"}, axis=1, inplace=True)
    df["竞价金额"] = df["竞价金额"].astype(float)
    df["竞价金额"] = round(df["竞价金额"] / 100000000, 2)

    df.rename({"所属同花顺行业": "行业"}, axis=1, inplace=True)

    df = df[df["昨日成交额"] > 5]
    # 添加“类型”列，并筛选显示的列
    df["类型"] = "隔日弱转强"
    df = df[["股票简称", "股票代码", "竞价涨跌幅", "类型", "跌幅", "昨日成交额", "连板", "竞价金额", "市值", "行业",
             "所属概念"]]

    # 返回最终结果
    return df


def getJieLi(query_date):
    # 获取前两个交易日
    pre_trade_date = get_pre_trade_date(query_date)
    pre_2_trade_date = get_pre_trade_date(pre_trade_date)

    # 构造查询问句
    question = f'''
    {pre_trade_date}涨停,非ST，行业，非北交所，{query_date}集合竞价涨跌幅,{pre_trade_date}成交额,{pre_trade_date}连续涨停天数,{query_date}竞价成交额,
    {pre_trade_date}市值，{pre_trade_date}最终涨停时间，概念
    '''
    print(question)

    # 使用问财获取数据
    df = pywencai.get(question=question)

    # print(df.columns)

    df[f"最终涨停时间[{pre_trade_date}]"] = pd.to_datetime(df[f"最终涨停时间[{pre_trade_date}]"].str.strip(),
                                                           format='%H:%M:%S').dt.time
    time_threshold = pd.to_datetime("11:25:00", format='%H:%M:%S').time()
    df = df[df[f"最终涨停时间[{pre_trade_date}]"] > time_threshold]

    # 排序并重命名列
    df = df.sort_values(by=f"分时涨跌幅:前复权[{query_date} 09:25]", ascending=False)
    df.rename({f"分时涨跌幅:前复权[{query_date} 09:25]": "竞价涨跌幅"}, axis=1, inplace=True)

    df.rename({f"连续涨停天数[{pre_trade_date}]": "连板"}, axis=1, inplace=True)
    df["连板"] = df["连板"].astype(int)

    df.rename({f"成交额[{pre_trade_date}]": "昨日成交额"}, axis=1, inplace=True)
    df.rename({f"竞价金额[{query_date}]": "竞价金额"}, axis=1, inplace=True)
    df["昨日成交额"] = df["昨日成交额"].astype(float)
    df["昨日成交额"] = round(df["昨日成交额"] / 100000000, 2)
    df["竞价金额"] = df["竞价金额"].astype(float)
    df["竞价金额"] = round(df["竞价金额"] / 100000000, 2)

    df.rename({f"a股市值(不含限售股)[{pre_trade_date}]": "市值"}, axis=1, inplace=True)
    df["市值"] = df["市值"].astype(float)
    df["市值"] = round(df["市值"] / 100000000, 2)

    df.rename({"所属同花顺行业": "行业"}, axis=1, inplace=True)

    df["竞价涨跌幅"] = df["竞价涨跌幅"].astype(float)
    df = df.head(10)
    df = df[df["竞价涨跌幅"] > 2]
    # 添加“类型”列，并筛选显示的列
    df["类型"] = "弱板转强"

    df["跌幅"] = 0
    df = df[["股票简称", "股票代码", "竞价涨跌幅", "类型", "连板", "跌幅", "昨日成交额", "竞价金额", "市值", "行业",
             "所属概念"]]

    # 返回最终结果
    return df

def ruoZhuanQiang(query_date):
    df1 = getZhaBan(query_date)
    df2 = getJieLi(query_date)
    df = pd.concat([df1,df2])
    df = df.sort_values(by=["类型","竞价涨跌幅"],ascending=False)
    df_styled = df.style.set_properties(**{'text-align': 'right'})
    pd.set_option('display.max_columns', None)  # 不限制列数显示
    pd.set_option('display.width', 1000)  # 设定一个足够大的值


    return df

def getDxjl(query_date):
    query_date_format = datetime.strptime(query_date, "%Y%m%d").strftime("%Y-%m-%d")
    sql = f'''
        select * from stock.real_market_info_dxjl rmit where 
                trade_type ='逼近涨停' and stock_name not like '%%ST%%'
                and  `timestamp` > '{query_date_format} 09:30:00' and `timestamp`<'{query_date_format} 15:00:00'
                order by `timestamp`  asc
        '''

    # print(sql)
    df_dxjl = pd.read_sql(sql, engine)
    return df_dxjl




def analysisStock(code, query_date_format, query_time,df_dxjl):
    # 将时间字符串转换为 datetime 对象
    time_obj = datetime.strptime(query_time, "%H:%M:%S")

    # 创建表示30秒的 timedelta 对象
    delta = timedelta(seconds=240)

    # 计算前30秒和后30秒的时间
    before_30_seconds = (time_obj - delta).time()
    after_30_seconds = (time_obj + delta).time()

    sql = f'''
        select * from stock.real_market_info_tdx rmit where 
                code = '{code}'
                and  `timestamp` > '{query_date_format} {query_time}' and `timestamp`<'{query_date_format} {after_30_seconds.strftime('%H:%M:%S')}'
                order by `timestamp`  asc
        '''

    # print(sql)
    df_tdx = pd.read_sql(sql, engine)
    df_tdx = pd.merge(df_tdx, df_dxjl[["股票简称","股票代码","连续涨停天数","流通市值","成交额","5日乖离","10日乖离","20日乖离"]], how="left", left_on="code", right_on="股票代码")
    df_tdx["hsl"] = round(df_tdx["amount"] / 10000 / df_tdx["流通市值"] * 100, 2)
    df_tdx["zhsl"] = round(df_tdx["amount"] / 10000 / df_tdx["成交额"] * 100, 2)
    df_tdx["open"] = df_tdx["price"] / (1 + df_tdx["change"] / 100)
    df_tdx["per_price"] = df_tdx["amount"]*100/df_tdx["vol"]
    df_tdx["per_price_change"]=(df_tdx["per_price"]-df_tdx["open"])*100/df_tdx["open"]

    return df_tdx[["timestamp","price","股票简称","code", "change", "amount","per_price_change", "连续涨停天数", "hsl", "zhsl","5日乖离","10日乖离","20日乖离","流通市值","成交额"]]

def get_trading_days(start_date, end_date):
    """
    计算两个日期之间的交易日。
    :param start_date: 开始日期，格式为 'YYYYMMDD'
    :param end_date: 结束日期，格式为 'YYYYMMDD'
    :param trade_dates: 交易日列表
    :return: 交易日数量
    """
    # 将日期字符串转换为 datetime 对象
    start_dt = datetime.strptime(start_date, '%Y%m%d').date()
    end_dt = datetime.strptime(end_date, '%Y%m%d').date()
    trade_dates = ak.tool_trade_date_hist_sina()
    trade_dates = trade_dates['trade_date'].tolist()

    # 过滤出在指定日期范围内的交易日
    filtered_trade_dates = [date for date in trade_dates if start_dt <= date <= end_dt]
    formatted_dates = [date.strftime('%Y%m%d') for date in filtered_trade_dates]
    # 返回交易日数量
    return formatted_dates


def PanZhongJianKong(dxjl_df,panqian_df,query_date):
    logging.info(f"开始进行日内交易")
    dxjl_df = pd.merge(panqian_df, dxjl_df, how="inner", left_on=["股票简称"], right_on=["stock_name"])
    query_date_format = datetime.strptime(query_date, "%Y%m%d").strftime("%Y-%m-%d")
    df_dxjl = dxjl_df.sort_values(by="timestamp", ascending=True)
    stock_list = []
    fail_stock_list = []
    stock_list_set=set()
    desc=""
    for index, row in df_dxjl.iterrows():

        rdf = analysisStock(row["股票代码"], query_date_format, row["trade_time"], df_dxjl)

        if row["股票简称"] in stock_list_set:
            row["desc"] = "已经买入"
            # fail_stock_list.append(row)
            continue

        if row["流通市值"] < 30 and row["成交额"]<1:
            row["desc"] = desc + "|"+ "流通市值小于30亿或者成交额小于1亿"
            fail_stock_list.append(row)
            continue

        if row["连续涨停天数"] > 0:
            row["desc"] =  "连续涨停"
            fail_stock_list.append(row)
            continue

        # if row["20日涨幅"] > 30:
        #     row["desc"] = "20日涨幅大于30"
        #     fail_stock_list.append(row)
        #     continue
        #
        # if row["2日涨幅"] > 10:
        #     row["desc"] = "2日涨幅大于10"
        #     fail_stock_list.append(row)
        #     continue

        is20 = False
        if row["股票代码"].startswith("30") or row["股票代码"].startswith("688"):
            is20 = True
            row["desc"] = "20cm标的"
            fail_stock_list.append(row)



        zhsl_low = 50
        zhsl_high = 110
        change_low = 6

        time_index = 0

        for index, row_r in rdf.iterrows():

            if row_r['timestamp'] < datetime.now().replace(hour=10, minute=0, second=0, microsecond=0):
                zhsl_high = 90
            elif row_r['timestamp'] < datetime.now().replace(hour=11, minute=0, second=0, microsecond=0):
                zhsl_high = 100
            elif row_r['timestamp'] < datetime.now().replace(hour=14, minute=0, second=0, microsecond=0):
                zhsl_high = 110

            if (row_r["change"] > change_low and row_r["change"] < 9.9 and row_r["zhsl"] > zhsl_low and row_r["zhsl"] < zhsl_high
                    and row_r["amount"]>3000 and row_r["per_price_change"]>5.5 and row_r["change"]>row_r["per_price_change"] and
                    (row_r["change"] - row_r["per_price_change"] < 3) and
                    is20 == False):
                stock_list.append(row_r)  # if row["股票简称"] in stock_list:
                stock_list_set.add(row_r["股票简称"])
                break

            if row_r["zhsl"]>zhsl_high:
                row["desc"] = "自由换手太大"+str(row_r)
                fail_stock_list.append(row)
                break

            if row_r["zhsl"]<zhsl_low and time_index>200:
                row["desc"] = "自由换手太小"+str(row_r)
                fail_stock_list.append(row)
                break

            time_index = time_index + 1

    return pd.DataFrame(stock_list),pd.DataFrame(fail_stock_list)

def analysisByDate(query_date):
    qingxu_df = QingXu(query_date)
    ruozhuanqiang_df = ruoZhuanQiang(query_date)
    panqian_df = r.get(f"stock_panqian:{query_date}")
    if panqian_df is None:
        print("取新的复盘数据")
        panqian_df = PanQian(query_date)
    else:
        panqian_df = pickle.loads(panqian_df)

    dxjl_df = getDxjl(query_date)
    shouban_df,fail_shouban_df = PanZhongJianKong(dxjl_df,panqian_df,query_date)
    print(shouban_df)

    file_path = f"分析结果/{query_date}.xlsx"
    with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
        # 分别将DataFrame写入不同的sheet
        qingxu_df.to_excel(writer, sheet_name='情绪', index=False)
        ruozhuanqiang_df.to_excel(writer, sheet_name='弱转强', index=False)
        shouban_df.to_excel(writer, sheet_name='首板', index=False)
        fail_shouban_df.to_excel(writer, sheet_name='首板失败', index=False)



if __name__ == '__main__':
    #检查是否有命令行参数（除了脚本名称本身）
    if len(sys.argv) > 1:
        # 如果提供了命令行参数，使用第一个参数作为查询日期
        query_date = sys.argv[1]
    else:
        # 如果没有提供命令行参数，使用当前日期
        query_date = datetime.now().strftime('%Y%m%d')
    PanQian(query_date)



    # date_df = get_trading_days("20240313","20240401")
    # for v in date_df:
    #     PanQian(v)


